package com.geneqiao.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.geneqiao.jdbc.jpa.ProcdureParamater;
import com.geneqiao.jdbc.jpa.TableStruct;
import com.geneqiao.jdbc.pool.JDBCDataSource;
import com.geneqiao.jdbc.pool.JDBCMap;
import com.geneqiao.jdbc.util.DataUtils;
import com.geneqiao.jdbc.util.ObjectUtils;
import com.geneqiao.jdbc.util.ParameterType;
import com.geneqiao.jdbc.util.SQLType;

public class FastSession
{

	// 日志
	private static final Logger logger = Logger.getLogger(FastSession.class);

	JDBCDataSource jdbcDataSource = null;
	private static final String NULL = "null";

	public FastSession(JDBCDataSource dataSource)
	{
		this.jdbcDataSource = dataSource;
	}

	/***
	 * 
	 * @param t
	 *            要查询的对象
	 * @param sql
	 *            要执行的sql
	 * @return Model Collection
	 */
	public <T> List<T> Query(Class<T> t, String sql)
	{
		return Query(t, sql, NULL);
	}

	/***
	 * 
	 * @param t
	 *            要查询的对象
	 * @param sql
	 *            要执行的sql
	 * @param autoMap
	 *            是否字段转换列名进行映射
	 * @return 查询的对象集合
	 */
	public <T> List<T> Query(Class<T> t, String sql, Object... objects)
	{
		long start = getNow();
		List<T> vos = null;
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			Statement st = ObjectUtils.buildStatement(conn, sql, objects);
			ResultSet rs = ObjectUtils.buildResultSet(st, sql);
			Map<Integer, String> map;
			long start2 = getNow();
			String cacheKey = t.getName() + "_" + sql;
			if (JDBCMap.containsKey(cacheKey))
			{
				map = JDBCMap.get(cacheKey);
				if (logger.isDebugEnabled())
				{
					logger.debug("Load map from cache => " + (getNow() - start2) + "ns");
				}
			}
			else
			{
				map = DataUtils.buildColumnMap(t, rs.getMetaData());
				JDBCMap.put(cacheKey, map);
				if (logger.isDebugEnabled())
				{
					logger.debug("Build map => " + (getNow() - start2) + "ns");
				}
			}
			if (map.size() > 0)
			{
				vos = new ArrayList<T>();
				while (rs.next())
				{
					vos.add(DataUtils.copyValue(map, rs, t));
				}
			}
			rs.close();
			st.close();
			if (logger.isDebugEnabled())
			{
				logger.debug("SQL => " + sql);
				logger.debug("Result rows => " + (vos != null ? vos.size() : 0) + "; columns => " + map.size()
						+ "; time => " + (getNow() - start) + "ns");
			}
		}
		catch (SQLException e)
		{
			logger.error("Get result faild => " + sql, e);
		}
		finally
		{
			jdbcDataSource.colseConnection(conn);
		}
		return vos;
	}

	/***
	 * 
	 * @param sql
	 *            查询的SQL
	 * @return 查询的对象集合
	 */
	public List<?> QueryList(String sql)
	{
		return QueryList(sql, NULL);
	}

	/**
	 * 
	 * @param sql
	 * @param objects
	 *            parameters
	 * @return 查询的单列集合
	 */
	public List<?> QueryList(String sql, Object... objects)
	{
		long start = getNow();
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			Statement st = ObjectUtils.buildStatement(conn, sql, objects);
			ResultSet rs = ObjectUtils.buildResultSet(st, sql);
			if (rs.getMetaData().getColumnCount() > 0)
			{
				String typeName = rs.getMetaData().getColumnTypeName(1);
				if ("int".equals(typeName.toLowerCase()))
				{
					List<Integer> vos = new ArrayList<>();
					while (rs.next())
					{
						vos.add(rs.getInt(1));
					}
					return vos;
				}
				if ("varchar".equals(typeName.toLowerCase()))
				{
					List<String> vos = new ArrayList<>();
					while (rs.next())
					{
						vos.add(rs.getString(1));
					}
					return vos;
				}
			}
			rs.close();
			st.close();
			if (logger.isDebugEnabled())
			{
				logger.debug("SQL => " + sql);
				logger.debug("Times => " + (getNow() - start) + "ns");
			}
			return null;
		}
		catch (SQLException e)
		{
			logger.error("获取查询结果失败 =>" + sql, e);
			return null;
		}
		finally
		{
			jdbcDataSource.colseConnection(conn);
		}
	}

	/***
	 * 执行存储过程
	 * 
	 * @param sql
	 *            存储过程名
	 * @return 执行结果
	 */
	public boolean RunProcdure(String sql)
	{
		return RunProcdure(sql, null);
	}

	/***
	 * 执行存储过程
	 * 
	 * @param sql
	 *            存储过程名
	 * @param paras
	 *            存储过程参数
	 * @return 执行结果
	 */
	public boolean RunProcdure(String sql, List<ProcdureParamater> paras)
	{
		long start = getNow();
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			CallableStatement cs = ObjectUtils.buildStatement(conn, sql);
			if (paras != null)
			{
				for (ProcdureParamater para : paras)
				{
					switch (para.getParameterType())
					{
					case IN:
						cs.setObject(para.getSort(), para.getValue());
						break;
					case OUT:
						cs.registerOutParameter(para.getSort(), Types.JAVA_OBJECT);
						break;
					default:
						cs.setObject(para.getSort(), para.getValue());
						cs.registerOutParameter(para.getSort(), Types.JAVA_OBJECT);
						break;
					}
				}
				// 执行存储过程
				cs.execute();
				for (ProcdureParamater para : paras)
				{
					if (para.getParameterType() == ParameterType.OUT || para.getParameterType() == ParameterType.INOUT)
					{
						para.setValue(cs.getObject(para.getSort()));
					}
				}
			}
			else
			{
				cs.execute();
			}
			cs.close();
			if (logger.isDebugEnabled())
			{
				logger.debug("Procdure => " + sql);
				logger.debug("Times => " + (getNow() - start) + "ns");
			}
			return true;
		}
		catch (SQLException e)
		{
			logger.error("Run procdure faild =>" + sql, e);
			return false;
		}
		finally
		{
			jdbcDataSource.colseConnection(conn);
		}
	}

	public <T> T Single(Class<T> t, String sql)
	{
		return Single(t, sql, NULL);
	}

	/***
	 * 
	 * @param t
	 *            要查询的对象
	 * @param sql
	 *            要执行的sql
	 * @param autoMap
	 *            是否字段转换列名进行映射
	 * @return 返回单个对象
	 * 
	 */
	public <T> T Single(Class<T> t, String sql, Object... objects)
	{
		List<T> dataList = Query(t, sql, objects);
		if (dataList != null && dataList.size() == 1) { return dataList.get(0); }
		return null;
	}

	public Map<String, Object> QueryMap(String sql)
	{
		return QueryMap(sql, NULL);
	}

	/***
	 * 
	 * @param sql
	 *            要执行的sql
	 * @return 查询的键值对
	 */
	public Map<String, Object> QueryMap(String sql, Object... objects)
	{
		long start = getNow();
		Connection conn = null;
		Map<String, Object> map = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			Statement st = ObjectUtils.buildStatement(conn, sql, objects);
			ResultSet rs = ObjectUtils.buildResultSet(st, sql);
			if (rs.getMetaData().getColumnCount() != 2)
			{
				logger.error("字典查询只能包含两列字段");
				return null;
			}
			map = new HashMap<>();
			while (rs.next())
			{
				map.put(rs.getString(1), rs.getObject(2));
			}
			rs.close();
			st.close();
			if (logger.isDebugEnabled())
			{
				logger.debug("SQL => " + sql);
				logger.debug("Map<K,V> => " + map.size() + "; Times => " + (getNow() - start) + "ns");
			}
		}
		catch (SQLException e)
		{
			logger.error("Get map<k,v> faild =>" + sql, e);
		}
		finally
		{
			jdbcDataSource.colseConnection(conn);
		}
		return map;
	}

	public Object getSingle(String sql)
	{
		return getSingle(sql, NULL);
	}

	/***
	 * 
	 * @param sql
	 *            要执行的sql
	 * @return 返回单个值
	 */
	public Object getSingle(String sql, Object... objects)
	{
		long start = getNow();
		Connection conn = null;
		Object obj = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			Statement st = ObjectUtils.buildStatement(conn, sql, objects);
			ResultSet rs = ObjectUtils.buildResultSet(st, sql);
			if (rs.next())
				obj = rs.getObject(1);
			rs.close();
			st.close();
			if (logger.isDebugEnabled())
			{
				logger.debug("SQL => " + sql);
				logger.debug("Result => " + obj + "; Times => " + (getNow() - start) + "ns");
			}
		}
		catch (SQLException e)
		{
			logger.error("Get single faild =>" + sql, e);
		}
		finally
		{
			jdbcDataSource.colseConnection(conn);
		}
		return obj;
	}

	/***
	 * 
	 * @param sql
	 *            要执行的sql
	 * @return boolean
	 */
	public boolean Execute(String sql)
	{
		return Execute(sql, NULL);
	}

	/***
	 * 
	 * @param sql
	 *            要执行的sql
	 * @param objects
	 *            执行sql语句对应的参数
	 * @return boolean
	 */
	public boolean Execute(String sql, Object... objects)
	{
		long start = getNow();
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			Statement st = ObjectUtils.buildStatement(conn, sql, objects);
			Integer num;
			if (st instanceof PreparedStatement)
				num = ((PreparedStatement) st).executeUpdate();
			else
				num = st.executeUpdate(sql);
			st.close();
			if (logger.isDebugEnabled())
			{
				logger.debug("SQL => " + sql);
				logger.debug("Rows => " + num + ", Times => " + (getNow() - start) + "ns");
			}
			return true;
		}
		catch (SQLException e)
		{
			logger.error("Execute SQL faild => " + sql, e);
			return false;
		}
		finally
		{
			jdbcDataSource.colseConnection(conn);
		}
	}

	public boolean Execute(List<String> sqls)
	{
		// 默认用事务执行
		return Execute(sqls, true);
	}

	/***
	 * 
	 * @param sqls
	 * @return
	 * @throws SQLException
	 */
	public boolean Execute(List<String> sqls, boolean withTran)
	{
		long start = getNow();
		long start2 = 0L;
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			if (withTran)
				conn.setAutoCommit(false);
			Statement st = conn.createStatement();
			st.setQueryTimeout(5);
			Integer num = 0;
			for (String sql : sqls)
			{
				start2 = getNow();
				num = st.executeUpdate(sql);
				if (logger.isDebugEnabled())
				{
					logger.debug("SQL => " + (sql.length() > 100 ? sql.substring(0, 100) : sql));
					logger.debug("Rows => " + num + ", Times => " + (getNow() - start2) + "ns");
				}
			}
			st.close();
			if (withTran)
			{
				conn.commit();
				logger.debug("Commited");
			}
			if (logger.isDebugEnabled())
			{
				logger.debug("Executed, Nums => " + sqls.size() + ", Times => " + (getNow() - start) + "ns");
			}
			return true;
		}
		catch (SQLException e)
		{
			if (withTran)
				jdbcDataSource.rollback(conn);
			logger.error("Execute SQLs faild", e);
			return false;
		}
		finally
		{
			if (withTran)
				jdbcDataSource.setAutoCommit(conn);
			jdbcDataSource.colseConnection(conn);
		}
	}

	public boolean execute(List<Sql> sqlList)
	{
		// 默认用事务执行
		return execute(sqlList, true);
	}

	public boolean execute(List<Sql> sqlList, boolean withTran)
	{
		long start = getNow();
		long start2 = 0L;
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			if (withTran)
				conn.setAutoCommit(false);
			Statement st;
			Integer num = 0;
			for (Sql sql : sqlList)
			{
				start2 = getNow();
				st = ObjectUtils.buildStatement(conn, sql.getSqlString(), sql.getParameters());
				if (st instanceof PreparedStatement)
					num = ((PreparedStatement) st).executeUpdate();
				else
					num = st.executeUpdate(sql.getSqlString());
				if (logger.isDebugEnabled())
				{
					logger.debug("SQL => " + sql.getSqlString());
					logger.debug("Rows => " + num + ", Times => " + (getNow() - start2) + "ns");
				}
			}
			if (withTran)
			{
				conn.commit();
				logger.debug("committed");
			}
			if (logger.isDebugEnabled())
			{
				logger.debug("Executed, Nums => " + sqlList.size() + ", Times => " + (getNow() - start) + "ns");
			}
			return true;
		}
		catch (SQLException e)
		{
			if (withTran)
				jdbcDataSource.rollback(conn);
			logger.error("Execute sqls faild", e);
			return false;
		}
		finally
		{
			if (withTran)
				jdbcDataSource.setAutoCommit(conn);
			jdbcDataSource.colseConnection(conn);
		}
	}

	public boolean save(Object obj)
	{
		List<Object> objs = new ArrayList<>();
		objs.add(obj);
		return save(objs);
	}

	public boolean save(List<Object> objs)
	{
		long start = getNow();
		long start2 = 0L;
		Connection conn = null;
		try
		{
			conn = jdbcDataSource.getConnection();
			TableStruct map;
			PreparedStatement ps;
			Object result = null;
			ResultSet rs;
			int num = 0;
			for (Object obj : objs)
			{
				start2 = getNow();
				map = DataUtils.buildSQLMap(obj, SQLType.INSERT);
				if (map == null)
				{
					logger.error("获取对象" + obj + "的注解失败");
					continue;
				}
				ps = ObjectUtils.buildStatement(conn, map.getBuildMap(), map.hasGenerageKey());
				if (ps.executeUpdate() > 0 && map.hasGenerageKey())
				{
					rs = ps.getGeneratedKeys();
					if (rs.next())
						result = rs.getInt(1);
					DataUtils.setValue(obj, map.getGenerateKey(), result);
					rs.close();
				}
				ps.close();
				num++;
				if (logger.isDebugEnabled())
				{
					logger.debug("Saved, ID => " + result + ", Times => " + (getNow() - start2) + "ns");
				}
			}
			if (logger.isDebugEnabled())
			{
				logger.debug("Finish, total => " + objs.size() + ", saved => " + num + ", Times => "
						+ (getNow() - start) + "ns");
			}
			return true;
		}
		catch (SQLException e)
		{
			logger.error("Save object faild", e);
			return false;
		}
		finally
		{
			jdbcDataSource.colseConnection(conn);
		}
	}

	/**
	 * 更新对象
	 * 
	 * @param obj
	 * @param notNull
	 *            是否只更新不为NULL字段
	 * @return
	 */
	public boolean update(Object obj, boolean notNull)
	{
		long start = getNow();
		Connection conn = null;
		try
		{
			TableStruct map = DataUtils.buildSQLMap(obj, (notNull ? SQLType.UPDATENOTNULL : SQLType.UPDATE));
			if (map == null || !map.hasPrimaryKey())
				return false;
			conn = jdbcDataSource.getConnection();
			PreparedStatement ps = ObjectUtils.buildStatement(conn, map.getBuildMap());
			Integer num = ps.executeUpdate();
			ps.close();
			if (logger.isDebugEnabled())
			{
				logger.debug("Updated, Rows => " + num + ", Times => " + (getNow() - start) + "ns");
			}
			return true;
		}
		catch (SQLException e)
		{
			logger.error("Update object faild", e);
			return false;
		}
		finally
		{
			jdbcDataSource.colseConnection(conn);
		}
	}

	public boolean updateNotNull(Object obj)
	{
		// 只更新不为NULL的字段
		return update(obj, true);
	}

	public boolean delete(Object obj)
	{
		long start = getNow();
		Connection conn = null;
		try
		{
			TableStruct map = DataUtils.buildSQLMap(obj, SQLType.DELETE);
			if (map == null || !map.hasPrimaryKey())
				return false;
			conn = jdbcDataSource.getConnection();
			PreparedStatement ps = ObjectUtils.buildStatement(conn, map.getBuildMap());
			Integer num = ps.executeUpdate();
			ps.close();
			if (logger.isDebugEnabled())
			{
				logger.debug("Deleted, Rows => " + num + ", Times => " + (getNow() - start) + "ns");
			}
			return true;
		}
		catch (SQLException e)
		{
			logger.error("Delete object faild", e);
			return false;
		}
		finally
		{
			jdbcDataSource.colseConnection(conn);
		}
	}

	private static long getNow()
	{
		return System.nanoTime();
	}

	public static FastSession getInstence(JDBCDataSource dataSource)
	{
		return new FastSession(dataSource);
	}
}
